1. Field of the Invention
The present invention relates to a method, system, and program for accessing rows in one or more tables satisfying a search criteria.
2. Description of the Related Art
Prior art database programs include a feature referred to as cursors. A cursor is a named control structure used by an application program to point to a row of interest within some set of rows and allow an application to retrieve, update or delete rows in the set. A cursor points to rows from a database table that satisfy a structured query language (SQL) query against the table. The rows in the table that satisfy the SQL query comprise a result set of data. The SQL query may include an SQL SELECT statement and a WHERE clause to qualify rows according to one or more predicates. An application can then access data on a row-by-row basis from the result set. The cursor SELECT statement may specify a simple SQL query on a single table or an SQL join operation performed on multiple tables.
A cursor is invoked with a DECLARE cursor command. Upon receiving the declare cursor statement, the SQL engine scans the entire base table and inserts a copy of all base table rows satisfying the query results in a result set. Only after all the qualifying rows are materialized in the result set can requests to fetch data from the result set be processed. Following materialization of the result set, the application program may then issue fetch commands to move the current row position and fetch forward or backward by one or more rows. The requested row in the FETCH is returned directly from the materialized result table. The application may also update or delete rows in the result set, which updates or deletes are then applied to the base table
The above scrollable cursor and cursor database functions are often used on large desktop or database server systems that can generate a result set rather quickly. Moreover, any delays in materializing the result table often do not affect the performance of the desktop or server application program requesting the result set.
Although materialization time is not an important performance criteria for desktop and server database programs, the time to materialize a result set is a substantially important factor for small computer devices, such as hand held computers, e.g., palm top devices, personal digital assistance (PDA), telephony devices, etc. A user of a hand held computer may request a set of records from a database within the hand held computer that satisfy a search criteria, such as an address book or other personal records. Using prior art scrollable cursor techniques, the hand held computer would have to wait until the entire query against the base table was evaluated and the result set materialized before allowing the application program to access and present the data. For palm devices with limited computing capabilities and impatient users, such delays in making the requested data available to the application may be unacceptable to the user.
Accordingly, there is a need in the art for improved techniques for database searching to make queried records available to an application program.